package com.wangy.database.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.wangy.database.util.DBUtil;
import com.wangy.database.util.TableUtil;
import com.wangy.utils.StringUtil;

public class JdbcTemplateImpl implements JdbcTemplate {

	private PreparedStatement statement;

	private Connection connection;

	public JdbcTemplateImpl(Connection connection) {
		this.connection = connection;
	}

	@Override
	public int delete(String sql, Object... params) throws SQLException {
		getStatement(sql, params);
		int result = 0;
		try {
			result = this.statement.executeUpdate();
		} finally {
			DBUtil.close(statement, connection);
		}
		return result;
	}

	@Override
	public int insert(String sql, Object... params) throws SQLException {
		getStatement(sql, params);
		int result = 0;
		try {
			result = this.statement.executeUpdate();
		} finally {
			DBUtil.close(statement, connection);
		}
		return result;
	}

	@Override
	public int update(String sql, Object... params) throws SQLException {
		getStatement(sql, params);
		int result = 0;
		try {
			result = this.statement.executeUpdate();
		} finally {
			DBUtil.close(statement, connection);
		}
		return result;
	}

	@Override
	public <T> T queryUnique(String sql, Class<T> clazz, Object... params)
			throws SQLException {
		ResultSet rs = null;
		try {
			getStatement(sql, params);
			rs = this.statement.executeQuery();
			return TableUtil.getEntity(clazz, rs);
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	@Override
	public <T> List<T> queryList(String sql, Class<T> clazz, Object... params)
			throws SQLException {
		ResultSet rs = null;
		try {
			getStatement(sql, params);
			rs = this.statement.executeQuery();
			return TableUtil.getEntityList(clazz, rs);
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	@Override
	public <T> List<T> queryList(String sql, Class<T> clazz, Integer start,
			Integer size, Object... params) throws SQLException {
		return queryList(sql, clazz, start, size, null, params);
	}

	@Override
	public <T> List<T> queryList(String sql, Class<T> clazz, String orderBy,
			Object... params) throws SQLException {
		return queryList(sql, clazz, null, null, orderBy, params);
	}

	@Override
	public <T> List<T> queryList(String sql, Class<T> clazz, Integer start,
			Integer size, String orderBy, Object... params) throws SQLException {
		ResultSet rs = null;
		try {
			StringBuffer strBuffer = new StringBuffer(sql);
			if (start != null && size != null) {
				strBuffer.append(" limit "+start+","+size);
			} 
			if (StringUtil.isNotNullAndEmpty(orderBy)) {
				strBuffer.append("  order by "+orderBy);
			} 
			rs = this.statement.executeQuery();
			return TableUtil.getEntityList(clazz, rs);
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	@Override
	public <T> List<T> queryAll(Class<T> clazz) throws SQLException {
		ResultSet rs = null;
		try {
			String tableName = TableUtil.getTableName(clazz);
			String sql = "select * from " + tableName;
			getStatement(sql);
			rs = this.statement.executeQuery();
			return TableUtil.getEntityList(clazz, rs);
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	@Override
	public void execute(String sql) throws SQLException {
		getStatement(sql);
		try {
			this.statement.execute(sql);
		} finally {
			DBUtil.close(statement, connection);
		}
	}

	public PreparedStatement getStatement() {
		return statement;
	}

	public void setStatement(PreparedStatement statement) {
		this.statement = statement;
	}

	private void getStatement(String sql, Object... params) throws SQLException {
		this.statement = connection.prepareStatement(sql);
		if (params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
		}
	}

	@Override
	public <T> long queryCount(Class<T> clazz) throws SQLException {
		ResultSet rs = null;
		long count = 0;
		try {
			String tableName = TableUtil.getTableName(clazz);
			String sql = "select count(*) from " + tableName;
			getStatement(sql);
			rs = this.statement.executeQuery();
			rs.next();
			count = rs.getLong(1);
			return count;
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	@Override
	public long queryCount(String sql, Object... params) throws SQLException {
		ResultSet rs = null;
		long count = 0;
		try {
			getStatement(sql, params);
			rs = this.statement.executeQuery();
			rs.next();
			count = rs.getLong(1);
			return count;
		} finally {
			DBUtil.close(rs, statement, connection);
		}
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

}
